oracle存储过程中的变量 |
您所在的位置：网站首页 › oracle中where 字段=:变量 › oracle存储过程中的变量 |
|
通过学习，了解到了变量的一些知识。变量的分类如下： 存储过程中的变量： 一、标量类型 （有%TYPE） 二、复合类型 （1）记录类型 (有%ROWTYPE） （2）索引表类型（关联数组） （3）varry变长数组一、标量类型 标量类型，一种是常用的有NUMBER、CHAR 、VARCHAR2 、VARCHAR、NCHAR 、NVARCHAR2 、LONG 、DATE 、TIMESTAMP ,基本上oracle 数据库认识的类型都可以直接用了。 还有一些我觉得不常用的，PLS_INTEGER 、BINARY_INTEGER 、SIMPLE_INTEGER 、布尔类型（可以用来存储逻辑的值，不能用作定义表中的数据类型）。 另外还有一个最常用的 %TYPE 。 因为上一篇已经简单用过第一种，所以这里介绍一下%TYPE 。 %TYPE 这种方式定义的变量类型的方式和其他的有所不同，它利用已经存在的数据类型来定义新数据的数据类型。例如，当定义多个变量或常量时，只要前面使用过的数据类型，后边的变量就可以利用%TYPE 引用，最常见的就是把表中的字段类型作为变量或常量的数据类型。使用此种方式的好处有以下几点： 利用%TYPE 方式定义的变量类型和被引用的常量数据类型保持一致，如果以前是VARCHAR 类型，现在要改成NUMBER 类型，那就只需要直接改数据表中的类型即可，对已写好的存储过程 没有影响。还有个好处就是，存储过程 基本都会有表操作，这就避免了在数据传入的时候，引发数据溢出或不符的情况。比如数据库里是VARCHAR(1) 这样的，但是实际传入的可能是张三 这样的字符串，肯定会导致在执行到这行数据的时候引发异常。如果能用%TYPE,那在调用存储过程 的时候，就能抛出错误。如下图所示： sex LY_DS.LY_NB%TYPE ，表示: 定义一个变量sex，它的类型与表LY_DS中的字段LY_NB` 类型一致。 1 create or replace procedure test_select_procedure 2 (sex LY_DS.LY_NB%TYPE,countNum out number) 3 AS 4 BEGIN 5 select count(*) into countNum from ly_ds where LY_NB=sex; 6 dbms_output.put_line(countNum); 7 END; 二、复合类型 1、记录类型如果在存储过程中查询结果中，是多列的情况，我们可能需要查询到结果后，对其做引用名称.成员名称 这样取值。上一篇中说到过，可以使用into，即如下写法： 1 create or replace procedure test_select2_procedure 2 (sex varchar) 3 AS 4 countNum number(10); --别忘了写上具体的长度,并且以分号结束 5 maxId number(10); --别忘了写上具体的长度,并且以分号结束 6 BEGIN 7 select count(*),max(id) into countNum,maxId from ly_ds where LY_NB=sex; 8 dbms_output.put_line(countNum); 9 dbms_output.put_line(maxId); 10 END;上边的是一种写法，就是into 后边跟上定义好的多个标量类型 。但是如果字段很多呢？ 显而易见，再用这个into 就不合适了，记录类型 就是解决这个的。 （1）定义type 的声明写法 定义type 的写法为 type type_name is record(col_name col_type); 这样，括号里可以写多个，中间用 , 分割，但最后一个不能有 , ，具体如下： 1 set serveroutput on; 2 DECLARE 3 TYPE thomas IS RECORD --以TYPE type_name IS RECORD开始 --此处thomas是个变量类型 4 ( 5 v_ly_nl test.name%TYPE, 6 v_ly_mc varchar(100) --最后不要加, 7 ); --最后以 ;结束 8 v_obj thoams; --将新对象定义为刚才声明的类型 --给thomas这个变量实例化了一个名字叫v_obj 9 BEGIN 10 --into赋值给v_obj，会按照定义的type顺序赋值 11 select id,nameinto v_obj from test where id='2'; 12 dbms_output.put_line('第一个变量：'||v_obj.v_ly_nl); 13 dbms_output.put_line('第二个变量：'||v_obj.v_ly_mc); 14 END;输出结果如下： 1 第一个变量：22 2 第二个变量：王五（2）通过%ROWTYPE 的声明写法 %ROWTYPE 该类型是提取行记录时常用的存储数据的方式。这种声明方式，可以直接引用表中的行作为变量类型，它同%TYPE 在优点上类似，避免因表中字段的数据类型改变，而导致PL/SQL 块出错的问题。 下边是%ROWTYPE 的用法例子，挺简单的： 1 DECLARE 2 v_obj ly_ds%rowtype; --ROWTYPE不区分大小写 3 BEGIN 4 --into赋值给v_obj，会按照定义的type顺序赋值 5 select * into v_obj from ly_ds where id='2'; 6 dbms_output.put_line('第一个变量：'||v_obj.ly_nl); 7 dbms_output.put_line('第二个变量：'||v_obj.ly_mc); 8 END;1 DECLARE 2 v_obj ly_ds%rowtype; --ROWTYPE不区分大小写 3 BEGIN 4 --into赋值给v_obj，会按照定义的type顺序赋值 5 select * into v_obj from ly_ds where id='2'; 6 dbms_output.put_line('第一个变量：'||v_obj.ly_nl); 7 dbms_output.put_line('第二个变量：'||v_obj.ly_mc); 8 END;
注意一个地方，就是上边的into 前边，只能为 * ，如果要写为具体的列，那就要写全。 2、索引表类型（关联数组） 该类型与数组相似，利用键值查找对应的数据，但是这里的键值同我们真正的数组下标不同，这种索引表的下标，还可以为字符串，真正的数组下标都是数字。索引表中的数据可以是上边介绍过的标量类型，也可以是记录类型。当在赋值的过程中，对已存在的索引表下标重复赋值，则会替换以前的数据，这个很好理解。 以下是我学习的时候，记录的这部分内容，都写在了下边： 1 DECLARE 2 /** 3 *声明一个存储ly_ds整行数据的索引表，下标为数字， 4 *即 binary_integer 5 */ 6 type index_row_type is table of ly_ds%rowtype index by binary_integer; 8 9 /** 10 *声明一个存储字符串数据的索引表，下标也为数字， 11 *即 pls_integer 12 */ 13 type index_val_type is table of varchar2(10) index by pls_integer; 15 16 /** 17 *声明一个存储字符串数据的索引表，下标为字符串， 18 *即 varchar(100)、varchar(10),必须给固定大小 19 */ 20 type index_str_val_type is table of varchar2(100) index by varchar(10); 22 /** 23 *定义一个下标为数字，存ly_ds一行的变量 24 */ 25 v_row index_row_type; 26 /** 27 *定义一个下标为数字，存字符串的变量 28 */ 29 v_val index_val_type; 30 /** 31 *定义一个下标为字符串，存字符串的变量 32 */ 33 v_str_val index_str_val_type; 34 BEGIN 35 /** 36 *为下标为数字的 字符串索引表下标1赋值 37 */ 38 v_val(1) :='正数'; 39 /** 40 *为下标为数字的 字符串索引表下标-1赋值 41 */ 42 v_val(-1) :='负数'; 43 dbms_output.put_line('v_val中下标1的值：'||v_val(1)); 44 dbms_output.put_line('v_val中下标-1的值：'||v_val(-1)); 45 46 /** 47 *将改行数据赋值给行变量的下标1上 48 */ 49 select * into v_row(1) from ly_ds where id='2'; 50 dbms_output.put_line('v_row(1)中ly_mc的值：'||v_row(1).ly_mc); 51 dbms_output.put_line('v_row(1)中ly_nl的值：'||v_row(1).ly_nl); 52 53 /** 54 *为下标为字符串的 字符串索引表的下标one赋值 55 */ 56 v_str_val('one') :='java天下第一'; 57 /** 58 *为下标为字符串的 字符串索引表的下标test赋值 59 */ 60 v_str_val('test') :='java天下无敌'; 61 /** 62 *为下标为字符串的 字符串索引表的下标test1赋值 63 */ 64 v_str_val('test1') :='java太可怕了'; 65 dbms_output.put_line('v_str_val中下标one的值：'||v_str_val('one')); 66 dbms_output.put_line('v_str_val中下标test的值：'||v_str_val('test')); 67 dbms_output.put_line('v_str_val中第一个值的下标：'||v_str_val.first); 68 dbms_output.put_line('v_str_val中第一个下标对应的值：'||v_str_val(v_str_val.first)); 69 dbms_output.put_line('v_str_val中最后一个下标：'||v_str_val.last); 70 END;运行后会有下边的结果： 1 v_val中下标1的值：正数 2 v_val中下标-1的值：负数 3 v_row(1)中ly_mc的值：王五 4 v_row(1)中ly_nl的值：22 5 v_str_val中下标one的值：java天下第一 6 v_str_val中下标test的值：java天下无敌 7 v_str_val中第一个值的下标：one 8 v_str_val中第一个下标对应的值：java天下第一 9 v_str_val中最后一个下标：test1上边有个特别注意的问题，就是，定义下标和存储的值的时候，一定要给出大小，比如字符串，不能写成varchar ,必须要写成varchar(100) 这样有固定大小的类型。 上边我用了三种，一种是pls_integer 、binary_integer ，一种是ly_ds%rowtype ，这两种都有固定的大小，第一种是数字类型，它们有固定的大小，可以百度一下。如果定义了标量类型那种，必须要要给出大小，这个需要注意下。 3、varry变长数组 varry 数组，是另一种存储有序元素的集合。集合下标从1开始，比较适合较少的数据使用。具体如下： 它有一个注意的地方，就是数组在定义一个变量时候，一定要初始化 ，并且，在使用前 一定要先确定容量 。对于，在初始化时，已赋值的，可以不用定义存储的大小了。这点与java 中的数组是一样的。 1 DECLARE 2 /** 3 *声明一个最多容纳100个数的varry数组，注意，它的下标是从1开始的。 4 *即 binary_integer 5 */ 6 type array_type is varray(100) of varchar(100); 7 /** 8 *分别定义一个直接赋值的和两个未赋值的数组。 9 *注意：一定要初始化，但可以不赋值。对于没有赋值的这种数组，在用之前 10 *也一定要先确定容量。 11 */ 12 v_val_array array_type := array_type('one','two'); 13 v_val_array2 array_type := array_type(); 14 v_val_array3 array_type := array_type(); 15 BEGIN 16 /** 17 *获取第一个varry数组中的值 18 *varry的下标从1开始 19 */ 20 dbms_output.put_line('v_val_array中下标1的值：'||v_val_array(1)); 21 dbms_output.put_line('v_val_array中下标2的值：'||v_val_array(2)); 22 23 24 /** 25 *获取第二个varry数组中的值 26 *因为第二个varry没有初始化长度，所以通过extend方法， 27 *为该数组加一个空位 28 */ 29 v_val_array2.extend; 30 v_val_array2(1) :='aaa'; 31 v_val_array2.extend; 32 v_val_array2(2) :='bbb'; 33 v_val_array2.extend; 34 v_val_array2(3) :='ccc'; 35 dbms_output.put_line('v_val_array2中下标1的值：'||v_val_array2(1)); 36 dbms_output.put_line('v_val_array2中下标2的值：'||v_val_array2(2)); 37 dbms_output.put_line('v_val_array2中下标3的值：'||v_val_array2(3)); 38 39 /** 40 *获取第三个varry数组中的值 41 *因为第三个varry没有初始化长度，所以通过extend方法 42 *初始化空位 43 */ 44 45 /** 46 *获取第二个varry数组中的值 47 *因为第二个varry没有初始化长度，所以通过extend方法， 48 *为该数组初始化长度 49 */ 50 v_val_array3.extend(v_val_array2.count()); 51 v_val_array3(1) :='ddd'; 52 v_val_array3(2) :='eee'; 53 v_val_array3(3) :='fff'; 54 dbms_output.put_line('v_val_array3中下标1的值：'||v_val_array3(1)); 55 dbms_output.put_line('v_val_array3中下标2的值：'||v_val_array3(2)); 56 dbms_output.put_line('v_val_array3中下标3的值：'||v_val_array3(3)); 57 END;结果 1 v_val_array中下标1的值：one 2 v_val_array中下标2的值：two 3 v_val_array2中下标1的值：aaa 4 v_val_array2中下标2的值：bbb 5 v_val_array2中下标3的值：ccc 6 v_val_array3中下标1的值：ddd 7 v_val_array3中下标2的值：eee 8 v_val_array3中下标3的值：fff上边分别是三种情况，其中有extend ，可能新手会感到迷惑。 因为v_val_array2和v_val_array3 开始的时候，只是定义了变量，所以，在使用前，需要给出大小，extent 的用法，即是对数组的长度加1个空位 的意思。 如果想直接为v_val_array3给出固定大小，可以直接v_val_array3.extend(3) ，这样就会给v_val_array3 一个默认的存储大小为3 ，也可以通过已有数组的大小，即是上边的v_val_array3.extend(v_val_array2.count())。 虽然上边都是PL/SQL 那边的知识，但是它们在存储过程 中，都是通用的。变量这地方，浪费了挺长的时间，但是基础有利用后边的学习，我觉得浪费了这两天应该会对自己有用处的。
原文链接：https://blog.csdn.net/wohaqiyi/article/details/81697710 |
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |